import pymysql
import os
from dotenv import load_dotenv

# 加载环境变量
load_dotenv()


class DatabaseManager:
    def __init__(self):
        self.host = os.getenv('DB_HOST', 'localhost')
        self.port = int(os.getenv('DB_PORT', 3306))
        self.user = os.getenv('DB_USER', 'root')
        self.password = os.getenv('DB_PASSWORD', 'root123')
        self.database = os.getenv('DB_NAME', 'easyPythonDatabase')
        self.connection = None

    def connect(self):
        """建立数据库连接"""
        try:
            self.connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset='utf8mb4',
                cursorclass=pymysql.cursors.DictCursor
            )
            return True
        except Exception as e:
            print(f"数据库连接失败: {e}")
            return False

    def disconnect(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()

    def execute_query(self, query, params=None):
        """执行查询语句"""
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(query, params)
                result = cursor.fetchall()
                return result
        except Exception as e:
            print(f"查询执行失败: {e}")
            return None

    def execute_update(self, query, params=None):
        """执行更新语句"""
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(query, params)
                self.connection.commit()
                return True
        except Exception as e:
            print(f"更新执行失败: {e}")
            self.connection.rollback()
            return False

    def create_table(self):
        """创建学生成绩表"""
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS student_scores (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id VARCHAR(20) NOT NULL COMMENT '学号',
            course_id VARCHAR(20) NOT NULL COMMENT '课程编号',
            score DECIMAL(5,2) NOT NULL COMMENT '成绩',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_student_course (student_id, course_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """
        return self.execute_update(create_table_sql)